﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[spMonitorPerfmonGather]
#-- Purpose:		Gathers current performance metrics and stores in a table
#--					for future review
#--	Last Update:	08/10/2011
#--					For a complete history - please review comments in Version
#--					Control.
#-- Called By:		Scheduled Job
#------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[spMonitorPerfmonGather]
AS

SET NOCOUNT ON

--- Declare Local Variables
DECLARE @perf TABLE (object_name nvarchar(128), counter_name nvarchar(128), instance_name nvarchar(128), cntr_value bigint)

--- Gather Raw Stats (this is faster than doing lots of individual looks on sysperfinfo)
INSERT INTO @perf
	SELECT	LTRIM(RTRIM(object_name)),
			LTRIM(RTRIM(counter_name)),
			LTRIM(RTRIM(instance_name)),
			cntr_value 
	FROM	master.dbo.sysperfinfo (NOLOCK)
	WHERE	(
				LTRIM(RTRIM(object_name)) LIKE '%:Buffer Manager'
				OR LTRIM(RTRIM(object_name)) LIKE '%:General Statistics'
				OR LTRIM(RTRIM(object_name)) LIKE '%:Memory Manager'
				OR LTRIM(RTRIM(object_name)) LIKE '%:SQL Statistics'
				OR LTRIM(RTRIM(object_name)) LIKE '%:Locks'
				OR LTRIM(RTRIM(object_name)) LIKE '%:Access Methods'
				OR LTRIM(RTRIM(object_name)) LIKE '%:Databases'
			)
			AND (
				LTRIM(RTRIM(counter_name)) LIKE 'Buffer cache hit ratio%'
				OR LTRIM(RTRIM(counter_name)) = 'Page life expectancy'
				OR LTRIM(RTRIM(counter_name)) = 'User Connections'
				OR LTRIM(RTRIM(counter_name)) LIKE '%Memory (KB)'
				OR LTRIM(RTRIM(counter_name)) LIKE '%/sec'
				OR LTRIM(RTRIM(counter_name)) LIKE '%(ms)'
			)
			AND (
				instance_name = '_Total'
				OR LTRIM(RTRIM(instance_name)) = ''
			)

--- Insert into the data store
INSERT INTO dbo.tblPerfmon
	SELECT	GETDATE() date_inserted,
			CAST((10000 
				* (select cntr_value from @perf where object_name like '%:Buffer Manager' and counter_name = 'Buffer cache hit ratio')
				/ (select cntr_value from @perf where object_name like '%:Buffer Manager' and counter_name = 'Buffer cache hit ratio base'))
				/ 100.00 as numeric(6, 2)) buffer_cache_hit_ratio,
			CAST((select cntr_value from @perf where object_name like '%:Buffer Manager' and counter_name = 'Page life expectancy') as bigint) page_life_expectancy,
			CAST((select cntr_value from @perf where object_name like '%:General Statistics' and counter_name = 'Logins/sec') as bigint) logins_per_sec,
			CAST((select cntr_value from @perf where object_name like '%:General Statistics' and counter_name = 'Logouts/sec') as bigint) logouts_per_sec,
			CAST((select cntr_value from @perf where object_name like '%:General Statistics' and counter_name = 'User Connections') as bigint) user_connections,
			CAST((select cntr_value from @perf where object_name like '%:Memory Manager' and counter_name = 'Optimizer Memory (KB)') as bigint) optimizer_memory_KB,
			CAST((select cntr_value from @perf where object_name like '%:Memory Manager' and counter_name = 'SQL Cache Memory (KB)') as bigint) sql_cache_memory_KB,
			CAST((select cntr_value from @perf where object_name like '%:Memory Manager' and counter_name = 'Target Server Memory (KB)') as bigint) target_server_memory_KB,
			CAST((select cntr_value from @perf where object_name like '%:Memory Manager' and counter_name = 'Total Server Memory (KB)') as bigint) total_server_memory_KB,
			CAST((select cntr_value from @perf where object_name like '%:SQL Statistics' and counter_name = 'SQL Compilations/sec') as bigint) sql_compilations_per_sec,
			CAST((select cntr_value from @perf where object_name like '%:SQL Statistics' and counter_name = 'SQL Re-Compilations/sec') as bigint) sql_recompilations_per_sec,
			CAST((select cntr_value from @perf where object_name like '%:SQL Statistics' and counter_name = 'Batch Requests/sec') as bigint) batch_requests_per_sec,
			CAST((select cntr_value from @perf where object_name like '%:Locks' and counter_name = 'Number of Deadlocks/sec' and instance_name = '_Total') as bigint) number_of_deadlocks_per_sec,
			CAST((select cntr_value from @perf where object_name like '%:Locks' and counter_name = 'Average Wait Time (ms)' and instance_name = '_Total') as bigint) average_wait_time_ms,
			CAST((select cntr_value from @perf where object_name like '%:Locks' and counter_name = 'Lock Waits/sec' and instance_name = '_Total') as bigint) lock_waits_per_sec,
			CAST((select cntr_value from @perf where object_name like '%:Locks' and counter_name = 'Lock Wait Time (ms)' and instance_name = '_Total') as bigint) lock_wait_time_ms,
			CAST((select cntr_value from @perf where object_name like '%:Access Methods' and counter_name = 'Full Scans/sec') as bigint) full_scans_per_sec,
			CAST((select cntr_value from @perf where object_name like '%:Buffer Manager' and counter_name = 'Lazy writes/sec') as bigint) lazy_writes_per_sec,
			CAST((select cntr_value from @perf where object_name like '%:Buffer Manager' and counter_name = 'Page reads/sec') as bigint) page_reads_per_sec,
			CAST((select cntr_value from @perf where object_name like '%:Buffer Manager' and counter_name = 'Page writes/sec') as bigint) page_writes_per_sec,
			CAST((select cntr_value from @perf where object_name like '%:Buffer Manager' and counter_name = 'Checkpoint pages/sec') as bigint) checkpoint_pages_per_sec,
			CAST((select cntr_value from @perf where object_name like '%:Access Methods' and counter_name = 'Page splits/sec') as bigint) page_splits_per_sec,
			CAST((select cntr_value from @perf where object_name like '%:Databases' and counter_name = 'Transactions/sec' and instance_name = '_Total') as bigint) transactions_per_sec,
			CAST(@@CPU_BUSY as bigint) cpu_busy, 
			CAST(@@IO_BUSY as bigint) io_busy,
			CAST(@@IDLE as bigint) idle

SET NOCOUNT OFF